package test;

import java.io.File;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class DBFactory{

    private static Connection   connection;
    private static final String FILE  = "c:/lookas.db";
    public static final String  TABLE = "tree";

    public static Connection getConnection() throws Exception{
        if(connection == null){
            URL u = new File("lib/sqlitejdbc-v037-nested.jar").toURL();
            String classname = "org.sqlite.JDBC";
            URLClassLoader ucl = new URLClassLoader(new URL[]{u});
            Driver d = (Driver)Class.forName(classname, true, ucl)
                .newInstance();
            DriverManager.registerDriver(new DriverShim(d));
            connection = DriverManager.getConnection("jdbc:sqlite:" + FILE);
            Statement stat = connection.createStatement();
            try{
                stat
                    .executeUpdate("CREATE TABLE "
                        + DBFactory.TABLE
                        + " (id INTEGER PRIMARY KEY, parent INT, name TEXT, UNIQUE (parent, name));");
                stat.executeUpdate("INSERT INTO " + DBFactory.TABLE
                    + " (id) VALUES (0);");
                stat.close();
            }catch(Exception e){}
        }
        return connection;
    }

    public static PreparedStatement prepareStatement(String sql)
        throws Exception{

        return DBFactory.getConnection().prepareStatement(sql);
    }

    public static void close() throws SQLException{
        connection.close();
    }
}
